package com.kcsj.dao;

import com.kcsj.domain.user.Notice;
import com.kcsj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
 * @author 胡庆阳
 * 29th Nov. 2020
 */
public class NoticeDao {
    private static final NoticeDao noticeDao =
            new NoticeDao();
    private NoticeDao() {

    }

    public static NoticeDao getInstance() {
        return noticeDao;
    }

    public Collection<Notice> findAllByTypeAndUserId(String type,Integer userId,Connection conn) throws SQLException {
        //预编译方式进行查询
        Collection<Notice> notices = new ArrayList<>();
        String s = "select * from notice where type = ? and user_id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        preparedStatement.setInt(2,userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String content = resultSet.getString(3);
            String status = resultSet.getString(4);
            Notice notice = new Notice(id,type,content,status);
            notices.add(notice);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return notices;
    }

    public Collection<Notice> findAllByType(String type,Connection conn) throws SQLException {
        //预编译方式进行查询
        Collection<Notice> notices = new ArrayList<>();
        String s = "select * from notice where type = ? ";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String content = resultSet.getString(3);
            String status = resultSet.getString(4);
            Notice notice = new Notice(id,type,content,status);
            notices.add(notice);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return notices;
    }

    public Notice findNewNotice(String type,Integer total,Integer userId,Connection conn) throws SQLException {
        //预编译方式进行查询
        Notice notice = null;
        String s = "SELECT COUNT(id) as number FROM notice where type = ? and user_id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        preparedStatement.setInt(2,userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        if (resultSet.next()){
            int newNum = resultSet.getInt(1);
            if(newNum> total){
                Integer maxId = this.findMaxId(type,userId,conn);
                notice = this.find(maxId,conn);
            }
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return notice;
    }

    public Notice findNewNotice(String type,Integer total,Connection conn) throws SQLException {
        //预编译方式进行查询
        Notice notice = null;
        String s = "SELECT COUNT(id) as number FROM notice where type = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        if (resultSet.next()){
            int newNum = resultSet.getInt(1);
            if(newNum> total){
                Integer maxId = this.findMaxId(type,conn);
                notice = this.find(maxId,conn);
            }
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return notice;
    }

    public Integer findMaxId(String type,Integer userId,Connection conn) throws SQLException {
        //预编译方式进行查询
        Integer id = null;
        String s = "select max(id) from notice where type = ? and user_id = ? ";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        preparedStatement.setInt(2,userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        if (resultSet.next()){
            id = resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return id;
    }

    public Integer findMaxId(String type,Connection conn) throws SQLException {
        //预编译方式进行查询
        Integer id = null;
        String s = "select max(id) from notice where type = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,type);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        if (resultSet.next()){
            id = resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return id;
    }

    public Notice find(Integer id,Connection conn) throws SQLException {
        //预编译方式进行查询
        Notice notice = null;
        String s = "select * from notice where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据存到departments集合中
        if (resultSet.next()){
            String type = resultSet.getString(2);
            String content = resultSet.getString(3);
            String status = resultSet.getString(4);
            notice = new Notice(id,type,content,status);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return notice;
    }



    //向Notice表中添加数据，包含userId，只能某个用户看到
    public int add(Notice notice, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into notice(type,content,status,user_id) value (?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,notice.getType());
        preparedStatement.setString(2,notice.getContent());
        preparedStatement.setString(3,notice.getStatus());
        preparedStatement.setInt(4,notice.getUser().getId());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }

    //向Notice表中添加数据,不包含UserId
    public void addNoUser(Notice notice, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into notice(type,content,status) value (?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,notice.getType());
        preparedStatement.setString(2,notice.getContent());
        preparedStatement.setString(3,notice.getStatus());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
    }

    //修改notice，改为已读
    public boolean update(List<Notice> notices, Connection conn) throws SQLException {
        //预编译方式进行查询
        String s = "update notice set status = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        for (Notice notice : notices) {
            preparedStatement.setString(1,"已读");
            preparedStatement.setInt(2,notice.getId());
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        int i = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return i>0;
    }

    //在notice表根据id删除相应数据
    public boolean delete(Integer id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from notice where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
}
